﻿using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using UpLoadDataOnTime.DataBaseUtil;
using UpLoadDataOnTime.Entity;

namespace UpLoadDataOnTime.Model
{
    class GetZYData
    {
        IDatabase db = DbFactory.CreateDb();

        //读取配置文件中的连接字符串（需添加引用System.Configuration）
        private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
        private static string connStrP = ConfigurationManager.ConnectionStrings["dbConnStrP"].ConnectionString;
        private static string connStrL = ConfigurationManager.ConnectionStrings["dbConnStrL"].ConnectionString;

        //住院患者基本信息
        public List<ZYTbPatientInfo> GetZYTbPatientInfos(string BeginDate, string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM,                                   " +
                         "       '09' AS GRBSLX,                                            " +
                         "       RTRIM(PatientID) AS GRBSH,                                 " +
                         "       '01' AS ZJLX,                                              " +
                         "       ISNULL(M08,'-') AS ZJHM,                                   " +
                         "       '云南省第三人民医院' AS YYMC,                                " +
                         "       CASE M05 WHEN '男' THEN '1'                                " +
                         "                WHEN '女' THEN '2'                                " +
                         "       ELSE '9'                                                   " +
                         "       END AS XB,                                                 " +
                         "       M04 AS XM,                                                 " +
                         "       CASE b.FP8 WHEN '1' THEN '10'                              " +
                         "                  WHEN '2' THEN '20'                              " +
                         "                  WHEN '3' THEN '30'                              " +
                         "                  WHEN '4' THEN '40'                              " +
                         "       ELSE '90'                                                  " +
                         "       END AS HYZK,                                               " +
                         "       CONVERT(VARCHAR(10),M06,121) AS CSRQ,                      " +
                         "       b.FP17_Name AS CSD,                                        " +
                         "       CASE WHEN FP11 = '99' THEN NULL                            " +
                         "       ELSE FP11                                                  " +
                         "       END AS MZ,                                                 " +
                         "       '156' AS GJ,                                               " +
                         "       a.M09 AS SJHM                                              " +
                         "FROM dbo.MBASE a(NOLOCK)                                          " +
                         "JOIN dbo.BA_FPage b(NOLOCK) ON CONVERT(VARCHAR(15),a.M01) = b.FP0 " +
                         "WHERE a.M11 >= @BeginDate AND a.M11 < @EndDate                    " +
                         "AND a.M52 IS NOT NULL                                             ";
            SqlParameter[] parameters = new SqlParameter[]
           {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
           };
            List<ZYTbPatientInfo> list = db.Context.QueryMany<ZYTbPatientInfo>(connStr, sql, parameters);
            return list;
        }

        //住院记录信息
        public List<ZYTbHospitalizationRecord> GetZYTbHospitalizationRecords(string BeginDate, string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM ,                   " +
                         "       FP0 AS JZLSH,                               " +
                         "       '云南省第三人民医院' AS YYMC,               " +
                         "       '09' AS GRBSLX,                             " +
                         "       FP_PatientID AS GRBSH,                      " +
                         "       CASE FP93 WHEN '6' THEN '5'                 " +
                         "       ELSE FP93                                   " +
                         "       END AS ABOXXDM,                             " +
                         "       FP87  AS RHXXDM,                            " +
                         "       CONVERT(VARCHAR(10),FP25,121) AS RYRQSJ,    " +
                         "       CONVERT(VARCHAR(10),FP31,121) AS CYRQSJ,    " +
                         "       '-' AS XBS,                                " +
                         "       '-' AS JWS,                                " +
                         "       '-' AS GMS,                                " +
                         "       '-' AS HYS                                 " +
                         "FROM dbo.BA_FPage (NOLOCK)                         " +
                         "WHERE FP31 >= @BeginDate AND FP31 < @EndDate " +
                         "ORDER BY FP31                                      ";
            SqlParameter[] parameters = new SqlParameter[]
           {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
           };
            List<ZYTbHospitalizationRecord> list = db.Context.QueryMany<ZYTbHospitalizationRecord>(connStr, sql, parameters);
            return list;
        }

        //出院小结信息
        public List<ZYTbDischargeAbstract> GetZYTbDischargeAbstracts(string BeginDate, string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM ,                   " +
                         "       FP0 AS JZLSH,                               " +
                         "       '云南省第三人民医院' AS YYMC,                  " +
                         "       '09' AS GRBSLX,                             " +
                         "       FP_PatientID AS GRBSH,                      " +
                         "       FP29 AS KSBH,                               " +
                         "       FP29_NAME AS KSMC,                          " +
                         "       CONVERT(VARCHAR(10),FP25,121) AS RYSJ,      " +
                         "       CONVERT(VARCHAR(10),FP31,121) AS CYSJ,      " +
                         "       CONVERT(INT,FP33) AS ZYTS,                  " +
                         "       FP36_YS AS MZZD,                            " +
                         "       FP37_YS AS RYZD,                            " +
                         "       FP37_NAME AS CYZD,                          " +
                         "       '未上传' AS CYYZ,                           " +
                         "       FP118_ZZYS AS ZZYSXM                        " +
                         "FROM dbo.BA_FPage (NOLOCK)                         " +
                         "WHERE FP31 >= @BeginDate AND FP31 < @EndDate       " +
                         "ORDER BY FP31                                      ";
            SqlParameter[] parameters = new SqlParameter[]
          {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
          };
            List<ZYTbDischargeAbstract> list = db.Context.QueryMany<ZYTbDischargeAbstract>(connStr, sql, parameters);
            return list;
        }

        //住院手术信息
        public List<ZYTbOperation> GetZYTbOperations(string BeginDate, string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM ,                           " +
                         "       KeyNo AS SSMXLSH,                                   " +
                         "       '云南省第三人民医院' AS YYMC,                       " +
                         "       ZYNum AS JZLSH,                                     " +
                         "       '09' AS GRBSLX,                                     " +
                         "       b.FP_PatientID AS GRBSH,                            " +
                         "       '2' AS MZZYBZ,                                      " +
                         "       '9' AS SSLX,                                       " +
                         "       a.OperationCode AS SSCZDM,                          " +
                         "       a.OperationName AS SSCZMC,                          " +
                         "       FP41 AS SSQZD,                                      " +
                         "       '未上传' AS SSHZD,                                  " +
                         "       CONVERT(VARCHAR(10),a.OperationDate,121) AS SSKSSJ, " +
                         "       CONVERT(varchar,GETDATE(),120) AS SSJSSJ,           " +
                         "       c.CODE AS SSYSBH,                                   " +
                         "       a.OperationOperName AS SSYSXM,                      " +
                         "       CASE a.CutCode WHEN '4' THEN '1'                    " +
                         "                      WHEN '5' THEN '2'                    " +
                         "                      WHEN '6' THEN '3'                    " +
                         "                      WHEN '7' THEN '1'                    " +
                         "                      WHEN '8' THEN '2'                    " +
                         "                      WHEN '9' THEN '3'                    " +
                         "                      WHEN '0' THEN '4'                    " +
                         "                      WHEN '10' THEN '1'                   " +
                         "                      WHEN '11' THEN '2'                   " +
                         "                      WHEN '12' THEN '3'                   " +
                         "       ELSE '4'                                            " +
                         "       END AS QKYHDJ                                       " +
                         "FROM dbo.BA_Operation a(NOLOCK)                            " +
                         "JOIN dbo.BA_FPage b(NOLOCK)ON a.ZYNum = b.FP0              " +
                         "JOIN dbo.YSCODE c(NOLOCK) ON a.OperationOperName = c.NAME  " +
                         "WHERE b.FP31 >= @BeginDate AND b.FP31 < @EndDate     ";
            SqlParameter[] parameters = new SqlParameter[]
         {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
         };
            List<ZYTbOperation> list = db.Context.QueryMany<ZYTbOperation>(connStr, sql, parameters);
            return list;
        }

        //住院检查信息
        public List<ZYTbCheckRecord> GetZYTbCheckRecords(string BeginDate, string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM,                              " +
                         "       '云南省第三人民医院' AS YYMC,                          " +
                         "       AccessionNumber AS JCBGID,                            " +
                         "       InPatientNum AS JZLSH,                                " +
                         "       '09' AS GRBSLX,                                       " +
                         "       PatientID_His AS GRBSH,                               " +
                         "       '2' AS MZZYBZ,                                        " +
                         "       '3' AS hzlxdm,                                        " +
                         "       ISNULL(CONVERT(VARCHAR(10),PatientStudyDate,120),CONVERT(VARCHAR(10),GETDATE(),120)) AS JCRQ,  " +
                         "       ReportDoctor AS JCYSQM,                               " +
                         "       ClinicName AS BGJGMC,                                 " +
                         "       ClinicName AS BGKSMC,                                 " +
                         "       ReportDoctor AS BGYSQM,                               " +
                         "       ISNULL(DiagnosesText,'未上传') AS JCBGJGKGSJ,         " +
                         "       ISNULL(DiagnosesText,'未上传') AS JCBGJGZGTS,         " +
                         "       CASE ModalityType WHEN 'DR' THEN '04'                 " +
                         "                         WHEN 'CT' THEN '01'                 " +
                         "                         WHEN 'B超' THEN '06'                " +
                         "                         WHEN '1胃肠镜' THEN '08'            " +
                         "                         WHEN '5胃肠镜' THEN '08'            " +
                         "                         WHEN 'B超-JR' THEN '06'             " +
                         "                         WHEN 'MG' THEN '10'                 " +
                         "                         WHEN 'MR' THEN '02'                 " +
                         "                         WHEN 'OT' THEN '10'                 " +
                         "                         WHEN 'US' THEN '06'                 " +
                         "                         WHEN '病理' THEN '07'               " +
                         "                         WHEN '肠镜' THEN '08'               " +
                         "                         WHEN '电子气管镜' THEN '08'         " +
                         "                         WHEN '胃肠镜' THEN '08'             " +
                         "       ELSE '10'                                             " +
                         "       END AS JCLX,                                          " +
                         "       Modality AS YQBM,                                     " +
                         "       ClinicName AS JCKSMC,                                 " +
                         "       CASE ScanWay WHEN '' THEN '未上传' ELSE ScanWay END AS JCBW,  " +
                         "       BodyPartExamined AS JCMC,                             " +
                         "       CASE WHEN Abnormality IS NULL THEN 2                  " +
                         "            WHEN Abnormality = 2 THEN 2                      " +
                         "            WHEN Abnormality = 3 THEN 3                      " +
                         "       ELSE Abnormality                                      " +
                         "       END AS YYS,                                           " +
                         "       DiagnosesText AS BGLCZD,                              " +
                         "       DiagnosesText AS YXBX,                                " +
                         "       DiagnosesText AS YXZD,                                " +
                         "       '1' AS SFYYY                                          " +
                         "FROM PatientSchedule a(NOLOCK)                               " +
                         "JOIN dbo.PatientBase b(NOLOCK) ON b.PatientID = a.PatientID  " +
                         "WHERE DelDate IS NULL                                        " +
                         "AND ReportDate IS NOT NULL                                   " +
                         "AND InPatientNum IS NOT NULL                                " +
                         "AND InPatientNum <> ''                                      " +
                         "AND ReportDate IS NOT NULL                                   " +
                         "AND ReportDate >= @BeginDate AND ReportDate < @EndDate ";
            SqlParameter[] parameters = new SqlParameter[]
        {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
        };
            List<ZYTbCheckRecord> list = db.Context.QueryMany<ZYTbCheckRecord>(connStrP, sql, parameters);
            return list;
        }

        //住院检验信息
        public List<ZYTbAssayRecord> GetZYTbAssayRecords(string BeginDate, string EndDate)
        {
            string sql = "select '43120477700' as YLJGDM,                                                                                                                         " +
                         "       CONVERT(char(8),a.date_test,112) + a.YBH as JYBGID,                                                                                              " +
                         "       '云南省第三人民医院' AS YYMC,                                                                                                                      " +
                         "       a.Patient_Code as JZLSH,                                                                                                                         " +
                         "       '09' as GRBSLX,                                                                                                                                  " +
                         "       a.Patient_Code as GRBSH,                                                                                                                         " +
                         "       CASE a.patient_kind WHEN '1' THEN a.patient_kind                                                                                                 " +
                         "                           WHEN '2' THEN '1'                                                                                                            " +
                         "                           WHEN '0' THEN '2'                                                                                                            " +
                         "                           WHEN '8' THEN '2'                                                                                                            " +
                         "       ELSE '9'                                                                                                                                         " +
                         "       END as MZZYBZ,                                                                                                                                   " +
                         "       (case a.Patient_Kind  when '1' then a.Patient_Code when '2' then a.Patient_Code else NULL end) as MJZH,                                          " +
                         "       (case a.Patient_Kind  when '0' then a.Patient_Code when '8' then a.Patient_Code else NULL end)  as ZYH,                                          " +
                         "       CASE ISNULL(a.patient_kind,NULL) WHEN '1' THEN a.patient_kind                                                                                    " +
                         "                                      WHEN '2' THEN a.patient_kind                                                                                      " +
                         "                                      WHEN '0' THEN '3'                                                                                                 " +
                         "                                      WHEN '8' THEN '3'                                                                                                 " +
                         "       ELSE '4'                                                                                                                                         " +
                         "       END as HZLXDM,                                                                                                                                   " +
                         "       A.YBH AS JYBBH,                                                                                                                                  " +
                         "       d.Name as JYXMMC,                                                                                                                                " +
                         "       (select name from ut_dict_user where User_ID = A.reporter) as JYYSQM,                                                                            " +
                         "       CONVERT(VARCHAR(10),A.Date_Check,120) AS JYRQ,                                                                                                   " +
                         "       ISNULL((select Group_Name from UT_DICT_Group  where Group_Code =(select check_group from ut_dict_report  where code = LEFT(A.YBH,2))),'-') AS BGKSMC, " +
                         "       '-' AS JYBGJG,                                                                                                                                  " +
                         "       A.Kind AS BBDM,                                                                                                                                  " +
                         "       (select Kind_Name from UT_Check_Module_Kind where Kind_Code = a.kind ) AS BBMC,                                                                  " +
                         "       ISNULL((select check_group from ut_dict_report  where code = LEFT(A.YBH,2)),'9999') AS BGDLBBM,                                                  " +
                         "        (select report_name from ut_dict_report where code = LEFT(A.YBH,2)) AS BGDLB                                                                    " +
                         "from   ut_check_patient a(NOLOCK) left outer join ut_check_collection c (nolock) on a.date_test = c.date_test and a.ybh = c.ybh, UT_DICT_Project_Kind d " +
                         "WHERE  a.Flag_Send ='5' and c.Item_Check = d.Code                                                                                                       " +
                         "AND a.Collection_time >= @BeginDate AND a.Collection_time < @EndDate                                                                                    " +
                         "AND a.patient_kind IN ('0','8',NULL)                                                                                                                    ";
             SqlParameter[] parameters = new SqlParameter[]
             {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
             };
                List<ZYTbAssayRecord> list = db.Context.QueryMany<ZYTbAssayRecord>(connStrL, sql, parameters);
                return list;
        }

        //住院检验结果指标信息
        public List<ZYTbAssayQuotaResult> GetZYTbAssayQuotaResults(string BeginDate,string EndDate)
        {
            string sql = "SELECT     '43120477700' as YLJGDM,                                                                                                                                                       " +
                         "           convert(char(8),r.test_date,112) + r.YBH AS JYZBLSH,                                                                                                                           " +
                         "           '云南省第三人民医院' AS YYMC,                                                                                                                                                    " +
                         "           convert(char(8),r.test_date,112) + r.YBH AS JYBGID,                                                                                                                            " +
                         "           convert(VARCHAR(10),r.test_date,121) as BGRQ,                                                                                                                                  " +
                         "           (select name from dbo.ut_dict_user where user_id = p.reporter) as JCRXM,                                                                                                       " +
                         "           r.item_code as YBSFDM,                                                                                                                                                         " +
                         "           'ZZZZZZZZZZZZZZZ' AS YZMXBMYB,                                                                                                                                                 " +
                         "CASE (select test_mothod_name from ut_dict_method where test_mothod in(select test_method from ut_dict_items where item_code=r.item_code)) WHEN '' THEN '未上传' ELSE ISNULL((select test_mothod_name from ut_dict_method where test_mothod in(select test_method from ut_dict_items where item_code=r.item_code)),'未上传') END AS JCFFMC, " +
                         "CASE (select Item_Name from dbo.UT_DICT_Items where Item_Code=r.Item_Code) WHEN '' THEN '未上传' ELSE ISNULL((select Item_Name from dbo.UT_DICT_Items where Item_Code=r.Item_Code),'未上传') END AS JCZBMC,                                                                              " +
                         "            case (select Value_Type from dbo.UT_DICT_Items where Item_Code = r.Item_Code) when '1' then '1' when '2' then '2' when '3' then '2' when '4' then '2' else '9' end as ZBJGLX, " +
                         "           r.Test_Value2 as JCJGZB,                                                                                                                                                       " +
                         "           dbo.fun_share_midat(r.Text_range,1,'-') as CKSX,                                                                                                                               " +
                         "           dbo.fun_share_midat(r.Text_range,0,'-') as CKXX,                                                                                                                               " +
                         "           r.Test_Value2 AS DXCKJG,                                                                                                                                                       " +
                         "           '项' as JLDW                                                                                                                                                                   " +
                         "FROM       dbo.UT_Check_Result AS r,dbo.ut_check_patient as p                                                                                                                             " +
                         "WHERE      p.Date_test = r.Test_Date and p.YBH = r.YBH and p.flag_send = '5'                                                                                                              " +
                         "AND r.Test_Date >= @BeginDate AND r.Test_Date < @EndDate ";
            SqlParameter[] parameters = new SqlParameter[]
           {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
           };
            return db.Context.QueryMany<ZYTbAssayQuotaResult>(connStrL, sql, parameters);
        }
    }
}
